﻿CREATE PROCEDURE [dbo].[proc_Customer_GetDepartmentList]
(
	@StartDate nvarchar(50),
	@EndDate nvarchar (50),
	@CompanyId int,
	@DepartmentId int,
	@Stext nvarchar(50),
	@StartIndex int,
	@EndIndex int
)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepartmentId

	--取该部门所有的员工
	;WITH List2 As(
--		Select e.UserName,e.EName,d.DName From Employee e,(Select Id,DName From Department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T) d
--		Where e.Departmentid=d.id
		Select e.UserName,e.EName,IsNull(d.DName,'') As DName From Employee e
		Left Join Department d On e.Departmentid=d.id
		Where e.CompanyId=@CompanyId And Left(d.bmbh,Len(@bmbh_T))=@bmbh_T
	),
	--取这些员工保护或跟踪中的客户
	List As(Select ROW_NUMBER() OVER (Order By C.protectdate desc,C.Id DESC)AS Rown,
		C.Id,C.CustomerId,C.CustomerName,Project,C.ProtectDate,ProtectId,
	    List2.DName As DepartmentName,List2.EName As ProtectName,
		C.SourceId,C.IndustryOneId,C.IndustryTwoId,C.TypeId
		From Customer C,List2
		Where CompanyId=@CompanyId And C.ProtectDate>=@sRq And C.ProtectDate<=@eRq
		And C.Audit = 1 And C.ProtectName=List2.UserName
		And (@Stext='' Or List2.EName Like '%' + @SText + '%' Or C.CustomerName Like '%' + @SText + '%')
	)

	Select Id,CustomerId,CustomerName,
	IsNull((Select Top 1 Title From Customer_Industry Where Id=List.IndustryOneId),'...') As IndustryOne,
	IsNull((Select Top 1 Title From Customer_Industry Where Id=List.IndustryTwoId),'...') As IndustryTwo,
	IsNull((Select Top 1 Title From Customer_Type Where Id=List.TypeId),'...') As Type,
	Project,ProtectName,ProtectDate,DepartmentName,ProtectId As Row,
	IsNull((Select Title From Customer_Source Where Id=List.SourceId),'...') As Source,
	(Select Count(0) From List) As RecordCount From List
	Where Rown Between @StartIndex And @EndIndex Order By Rown
End
